package org.tpc.yn.service;

import com.alibaba.fastjson2.JSONObject;

import lombok.extern.slf4j.Slf4j;
import oracle.jdbc.OracleTypes;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.stereotype.Component;
import org.tpc.yn.db.DBManager;
import org.tpc.yn.em.EnumInOut;
import org.tpc.yn.utils.JDBCUtil;
import org.tpc.yn.utils.JSONUtil;
import org.tpc.yn.utils.StringUtil;
import org.tpc.yn.vo.FldListVo;
import org.tpc.yn.vo.ProcedureParamVo;
import org.tpc.yn.vo.ProcedureVo;

import javax.sql.DataSource;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

@Component
@Slf4j
public class SimpleProcedureService {

    @Autowired
    private DataSource dataSource;

    public List<ProcedureParamVo> getParamsByProcedureName(Connection connection, String procedureName)  {
        try {
            String sql = "";
            if(JDBCUtil.isOracle(connection)){
                sql = "SELECT A.ARGUMENT_NAME  \"ARGUMENTNAME\",A.DATA_TYPE \"DATATYPE\",A.IN_OUT \"INOUT\" " +
                        " FROM USER_ARGUMENTS A WHERE OBJECT_NAME = UPPER(?) " +
                        " AND A.ARGUMENT_NAME IS NOT NULL " + //排除输出参数中为记录集类型时的记录集参数
                        " ORDER BY A.SEQUENCE";
                QueryRunner queryRunner = new QueryRunner();
                return queryRunner.query( connection, sql, procedureName, new BeanListHandler<ProcedureParamVo>(ProcedureParamVo.class));
            }else{
                sql = "SELECT P.NAME AS \"ARGUMENTNAME\",T.NAME AS \"DATATYPE\",CASE WHEN P.IS_OUTPUT  = 0 THEN 'IN' ELSE 'OUT' END AS \"INOUT\" " +
                        "FROM  SYS.PARAMETERS P " +
                        "INNER JOIN SYS.TYPES T ON P.SYSTEM_TYPE_ID = T.SYSTEM_TYPE_ID AND P.USER_TYPE_ID = T.USER_TYPE_ID  " +
                        "INNER JOIN SYS.PROCEDURES F ON P.OBJECT_ID = F.OBJECT_ID " +
                        "WHERE F.[NAME] = UPPER( '"+ procedureName +"'  ) " +
                        "AND P.NAME IS NOT NULL " +
                        "ORDER BY P.PARAMETER_ID";
                QueryRunner queryRunner = new QueryRunner();
                return queryRunner.query( connection, sql, new BeanListHandler<ProcedureParamVo>(ProcedureParamVo.class) );
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }



    public Map<String , Object> callProcedure(String packageName, JSONObject paramsJSON ,Boolean to){

        Connection conn = null;
        ProcedureVo procVo = null;
        Map<String , Object> dataMap = null;
        CallableStatement statement = null;
        try {
            conn = dataSource.getConnection();
            procVo = this.getCallSql(conn,packageName);
            statement = conn.prepareCall(procVo.getCallSql());
            List<ProcedureParamVo> paramList = procVo.getParams();
            registerParameter(statement , paramList , paramsJSON);
            statement.execute();
            if(JDBCUtil.isOracle(conn)){
                dataMap = outResultSet(conn, statement , paramList, to);
            }else{
                dataMap = outSqlServerResultSet(conn, statement , paramList ,to);
            }

        } catch (SQLException e) {
            log.info("调用存储过程【" + packageName + "】出现错误：{}", e.getMessage());
            log.info("返回参数："+ StringUtil.subStrSQL(e.getLocalizedMessage()));
            dataMap = new HashMap<String , Object>();
            dataMap.put("result", StringUtil.subStrSQL(e.getLocalizedMessage()));//操作结果
            throw new RuntimeException(e.getMessage());
        } finally {
            if(statement != null){
                try {statement.close();} catch (SQLException e) {}
            }
            if(conn != null){
                try {
                    conn.close();
                } catch (SQLException e)
                {
                    e.printStackTrace();
                }
            }

        }
        return dataMap;
    }


    private Map<String , Object> outResultSet(Connection connection,CallableStatement statement , List<ProcedureParamVo> paramList,Boolean to) throws SQLException {
        Map<String , Object> dataMap = new HashMap<String , Object>();
        List<FldListVo> fldListVoList = new ArrayList<>();
        try {
            for (int i = 0 , lens = paramList.size() ; i < lens; i++) {
                ProcedureParamVo paramVo = paramList.get(i);
                String inOut = paramVo.getInOut();
                if(! EnumInOut.OUT.getKey().equalsIgnoreCase(inOut)){
                    continue;
                }
                Object object = null;

                object = statement.getObject(i + 1);

                if(object instanceof ResultSet){
                    ResultSet rs = (ResultSet) object;
                    List<Map<String, Object>> dataList = DBManager.resultSet2List(rs,true,to);
                    if (dataList == null) {
                        dataList = new ArrayList<Map<String, Object>>();
                    }
                    dataMap.put(paramVo.getArgumentName(), dataList);
                    log.info("{}",dataList);
                    Map<String, Object> stringObjectMap = null;
                    if(!dataList.isEmpty()){
                        stringObjectMap = dataList.get(0);
                    }else{
                        throw new RuntimeException("无数据");
                    }
                    if(stringObjectMap!=null){
//                        Set<String> strings = stringObjectMap.keySet();
//                        List<String> fieldList = new ArrayList<>();
//                        List<FldListVo> fldListVo = new ArrayList<>();
//                        for (String str : strings) {
//                            fldListVo.add(new FldListVo(str,null));
//                            fieldList.add(str.toUpperCase());
//                        }
//                        QueryRunner queryRunner = new QueryRunner();
//                        String sql = "SELECT FDNAME,FDDESC FROM FLDLIST WHERE UPPER(FDNAME) IN ( " + StringUtil.getPlaceholders(fieldList.size()) + " )";
//                        List<FldListVo> fldListVos = queryRunner.query(connection, sql, new BeanListHandler<>(FldListVo.class), fieldList.toArray());
//                        Map<String, String> fldListVoMap = new HashMap<>();
//                        for (FldListVo result : fldListVos) {
//                            fldListVoMap.put(result.getFdName().toUpperCase(), result.getFdDesc());
//                        }
//                        for (FldListVo vo : fldListVo) {
//                            String fdName = vo.getFdName().toUpperCase();
//                            // 从Map中获取匹配的数据
//                            String result = fldListVoMap.get(fdName);
//                            if (result != null) {
//                                vo.setFdDesc(result);
//                            }else{
//                                vo.setFdDesc(vo.getFdName());
//                            }
//                        }
//                        dataMap.put("fields", fldListVo);
                        continue;
                    }
                    rs.close();
                }
                dataMap.put(paramVo.getArgumentName(), object);
            }
        }catch (Exception e){
            throw new RuntimeException(e.getMessage());
        }
        return dataMap;
    }


    private Map<String , Object> outSqlServerResultSet(Connection connection,CallableStatement statement , List<ProcedureParamVo> paramList,Boolean to) throws SQLException {
        Map<String , Object> dataMap = new HashMap<String , Object>();
        List<FldListVo> fldListVoList = new ArrayList<>();
        try {
            Object object = null;
            object = statement.getResultSet();
            if(object instanceof ResultSet){
                ResultSet rs = (ResultSet) object;
                List<Map<String, Object>> dataList = DBManager.resultSet2List(rs,false,to);
                if (dataList == null) {
                    dataList = new ArrayList<Map<String, Object>>();
                }
                dataMap.put("DATASET", dataList);
                Map<String, Object> stringObjectMap = null;
                if(!dataList.isEmpty()){
                    stringObjectMap = dataList.get(0);
                }else{
                    throw new RuntimeException("无数据");
                }
//                if(stringObjectMap!=null){
//                    Set<String> strings = stringObjectMap.keySet();
//                    List<String> fieldList = new ArrayList<>();
//                    List<FldListVo> fldListVo = new ArrayList<>();
//                    for (String str : strings) {
//                        fldListVo.add(new FldListVo(str,null));
//                        fieldList.add(str.toUpperCase());
//                    }
//                    String fieldStr = "'"+String.join("','",  fieldList)+"'";
//                    QueryRunner queryRunner = new QueryRunner();
//                    String sql = "SELECT FDNAME,FDDESC FROM FLDLIST WHERE UPPER(FDNAME) IN ( " + fieldStr + ")";
//                    List<FldListVo> fldListVos = queryRunner.query(connection, sql, new BeanListHandler<>(FldListVo.class));
//                    Map<String, String> fldListVoMap = new HashMap<>();
//                    for (FldListVo result : fldListVos) {
//                        fldListVoMap.put(result.getFdName().toUpperCase(), result.getFdDesc());
//                    }
//                    for (FldListVo vo : fldListVo) {
//                        String fdName = vo.getFdName().toUpperCase();
//                        // 从Map中获取匹配的数据
//                        String result = fldListVoMap.get(fdName);
//                        if (result != null) {
//                            vo.setFdDesc(result);
//                        }else{
//                            vo.setFdDesc(vo.getFdName());
//                        }
//                    }
//                    dataMap.put("fields", fldListVo);
//                }
                rs.close();
            }

        }catch (Exception e){

            throw new RuntimeException(e.getMessage());
        }
        return dataMap;
    }

    public ProcedureVo getCallSql(Connection connection,String packageName){
        StringBuilder callBuilder = new StringBuilder();
        List<ProcedureParamVo> params = this.getParamsByProcedureName(connection,packageName);
        callBuilder.append("{call ").append(packageName).append("(");
        for (int i = 0 , lens = params.size() ; i < lens; i++) {
            if (i + 1 == lens) {
                callBuilder.append("?");
                break;
            }
            callBuilder.append("?,");
        }
        callBuilder.append(")}");
        log.info("拼接SQL:{}",callBuilder);
        return new ProcedureVo(callBuilder.toString() , params);
    }

    private void registerParameter(CallableStatement statement , List<ProcedureParamVo> paramList, JSONObject paramsJSON) throws SQLException {
        String params = "";
        for (int i = 0 , lens = paramList.size() ; i < lens; i++) {
            ProcedureParamVo paramVo = paramList.get(i);
            String inOut = paramVo.getInOut();

            if(EnumInOut.IN.getKey().equalsIgnoreCase(inOut)){
                if(paramsJSON.getString(paramVo.getArgumentName())!=null) {
                    String json = JSONObject.toJSONString(paramsJSON.get(paramVo.getArgumentName()));
                    if(JSONUtil.IsJSON(json)){
                        json = json;
                    }else {
                        json = paramsJSON.getString(paramVo.getArgumentName());
                    }
                    statement.setObject(i + 1, json);
                    params = params + paramVo.getArgumentName() +":"+ json + ",";
                }else{
                    if(paramVo.getDataType().equals("NUMBER")) {
                        statement.setObject(i + 1, 0);
                        params = params +  paramVo.getArgumentName() +":"+ 0 + ",";
                    }else{
                        statement.setObject(i + 1, "");
                        params = params + paramVo.getArgumentName() +":"+"'" + "',";
                    }
                }
            } else if(EnumInOut.OUT.getKey().equalsIgnoreCase(inOut)){
                String dataType = paramVo.getDataType();
                statement.registerOutParameter(i + 1, getOracleTypes(dataType));
            }

        }
        log.info("参数生成：【{}",params.substring(0,params.length()-1)+"】");
    }

    private int getOracleTypes(String dataType) {
        final Map<String , Integer> dataTypeMap = new HashMap<String , Integer>();
        dataTypeMap.put("VARCHAR2", OracleTypes.VARCHAR);
        dataTypeMap.put("CHAR", OracleTypes.VARCHAR);
        dataTypeMap.put("CLOB", OracleTypes.CLOB);
        dataTypeMap.put("DATE", OracleTypes.DATE);
        dataTypeMap.put("NUMBER", OracleTypes.NUMBER);
        dataTypeMap.put("REF CURSOR", OracleTypes.CURSOR);
        dataTypeMap.put("PL/SQL RECORD", OracleTypes.CURSOR);
        if(! dataTypeMap.containsKey(dataType)){
            return OracleTypes.OTHER;
        }
        return dataTypeMap.get(dataType);
    }
}
